#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";

my $station=$ARGV[0];
my $platform_handle=$ARGV[1];
my $timezone=tellmeedt();
my $timeedt=tellmeinterval();
my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $tmpDir="/var/www/html/data/";

my ($sqlsta,$sth1);

 my $dbh=getdbConn();
 if($dbh==0){ die "Cannot connect to database!\n";}

 my (@staarray,@handlearray);
 $sqlsta="select short_name,platform_handle from platform";
 $sth1 = $dbh->prepare($sqlsta);
 $sth1->execute();
 
 while (my @row = $sth1->fetchrow_array) {
     push(@staarray,$row[0]);
     push(@handlearray,$row[1]);
 }
 $sth1->finish();
 
 for(my $a=0;$a<@staarray;$a++){

    my $station=$staarray[$a];
    my $platform_handle=$handlearray[$a];

    my ($standardname,$lastUpdate);

    my $file=$tmpDir.$station."1.html";
    print $file."\n";
    open(HTML,">$file");

    my (@sensor,@mtype,@namearray,@mtime,@uomarray,@zarray);
    $sqlsta="select sensor.row_id,sensor.fixed_z,m_type.row_id,obs_type.standard_name,uom_type.standard_name from sensor 
         left join m_type on m_type.row_id=sensor.m_type_id
         left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
         left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
         left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
         left join platform on sensor.platform_id=platform.row_id
         where platform.platform_handle = '$platform_handle' and obs_type.multiple=0 and sensor.active=true order by m_type.row_id asc";

   $sth1 = $dbh->prepare($sqlsta);
   $sth1->execute();
 
   while (my @row = $sth1->fetchrow_array) {
            push(@sensor,$row[0]);
            push(@mtype,$row[2]);
            $standardname=$row[3];

            my($temptitle,$formula)=uom_convert($row[3],$row[4]);
            push(@zarray,$formula);
            push(@uomarray,$temptitle);

            my $i=0;
            my @array1=split(/_/,$standardname);
            $standardname="";
            for($i=0;$i<@array1;$i++){
               $array1[$i]=ucfirst($array1[$i]);
               $standardname=$standardname.$array1[$i]." ";
            }
            if($row[1] && ($row[1]-0)<=0){ $standardname=$standardname." (Depth: $row[1] m)"; }

            push(@namearray,$standardname);  
   }
  


  $sqlsta="select distinct m_date from multi_obs where platform_handle='$platform_handle' and age(m_date)<'1 day' and (m_date-'$timeedt hour'::interval)<current_timestamp order by m_date desc limit 10";
  $sth1 = $dbh->prepare($sqlsta);
  $sth1->execute();
 
  while (my @row = $sth1->fetchrow_array) {
     push(@mtime,$row[0]);
  }

   my $dststring=tellmeedt();
   print HTML "<table width=100% cellspacing=1 cellpadding=1>";
   print HTML "<tr bgcolor=#ffffdd><td>MM-DD-YY<br>HH:MM ($dststring)</td>";
   
   for(my $j=0;$j<@mtime;$j++){
         
         $lastUpdate=timeConvert($mtime[$j],$dststring);
         $lastUpdate=substr($lastUpdate,5,5)."-".substr($lastUpdate,2,2)."<br>".substr($lastUpdate,11,5);
         if(substr($lastUpdate,12,2)>=12){
             $lastUpdate=$lastUpdate." PM";
         }
         else{
             $lastUpdate=$lastUpdate." AM";
         }
         print HTML "<td>$lastUpdate</td>";
   }
   if(@mtime==0){
         for(my $j=0;$j<10;$j++){
               print HTML "<td>&nbsp; - &nbsp;</td>";
         }
   }
   print HTML "</tr>";

   
   for(my $i=0;$i<@sensor;$i++){
       

       if($i%2==1){
          print HTML "<tr class=bodytext bgcolor=#ffffdd><td><a href=typeQuery.php?station=$station&platform_handle=$platform_handle&m_type_id=$mtype[$i]&sensor_id=$sensor[$i]>$namearray[$i]</a>($uomarray[$i])</td>";
       }
       else {
          print HTML "<tr class=bodytext class=bodytext bgcolor=#eeeeee><td><a href=typeQuery.php?station=$station&platform_handle=$platform_handle&m_type_id=$mtype[$i]&sensor_id=$sensor[$i]>$namearray[$i]</a>($uomarray[$i])</td>";
         
       }
       
       for(my $j=0;$j<@mtime;$j++){
            
            $sqlsta="select m_value,qc_level from multi_obs where m_date='$mtime[$j]' and m_type_id=$mtype[$i] and sensor_id=$sensor[$i] and platform_handle='$platform_handle'";
            $sth1 = $dbh->prepare($sqlsta);
            $sth1->execute();
            my $m=0;

            while (my @row = $sth1->fetchrow_array) {
                my $sus="";
                if($row[1]==2){$sus="(suspect)";}
                if($row[1]==1){$sus="(bad)";}
                $row[0]=value_convert($zarray[$i],$row[0]);
                $row[0]=sprintf("%.2f",$row[0]);
                print HTML "<td align=left>&nbsp;$row[0] $sus</td>";;
                #print "<td align=left>$mtime[$j]\t &nbsp;$row[0] $sus</td>\n";; 
                $m++;
            }
            if($m==0){print HTML "<td align=left>&nbsp; - &nbsp;</td>";}

             $sth1->finish();
        }# end of mtime array 

        if(@mtime==0){
             for(my $j=0;$j<10;$j++){
                   print HTML "<td>&nbsp; - &nbsp;</td>";
             }
        }        

        print HTML "</tr>";
    } # end of sensor array

    if(@sensor%2==0){
      print HTML "<tr bgcolor=#eeeeee><td>YY-MM-DD<br>HH:MM (UTC)</td>";
    }
    else {
      print HTML "<tr bgcolor=#ffffdd><td>MM-DD-YY<br>HH:MM (UTC)</td>";
    }
    for(my $j=0;$j<@mtime;$j++){
         $lastUpdate=substr($mtime[$j],5,5)."-".substr($mtime[$j],2,2)."<br>".substr($mtime[$j],11,5);
         
         if(substr($lastUpdate,12,2)>=12){
             $lastUpdate=$lastUpdate." PM";
         }
         else{
             $lastUpdate=$lastUpdate." AM";
         }
         print HTML "<td>$lastUpdate </td>";
    }
    if(@mtime==0){
         for(my $j=0;$j<10;$j++){
               print HTML "<td>&nbsp; - &nbsp;</td>";
         }
    }
    print HTML "</tr>";

    print HTML "</table>"; 
    close(HTML); 

} # end of station array

 undef $sth1;
 $dbh->disconnect();



sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}


